<?php

class ReportController extends Controller
{
	/**
	 * @var string the default layout for the views. Defaults to 'column2', meaning
	 * using two-column layout. See 'protected/views/layouts/column2.php'.
	 */
	public $layout='column2';
	
	/**
	 * @return array action filters
	 */
	public function filters()
	{
		return array(
			//'accessControl', // perform access control for CRUD operations
			array('ESetReturnUrlFilter + admin, view',),
			array('SetSaveAttributesFilter + view, create, delete, update',),
		);
	}

	/**
	 * Lists all.
	 */
	public function actionIndex()
	{
		$users=User::model()->findAll();
		$this->render('index',array(
			'users'=>$users,
		));
		
		/*
		 * SELECT user.id AS id, user.username AS username, (

SELECT SUM( payment.payment_sum ) 
FROM yii_payment AS payment
WHERE payment.user_id = user.id
) AS payment, (

SELECT SUM( payacc.cost ) 
FROM yii_payacc AS payacc
WHERE payacc.user_id = user.id
) AS payment */
		
		
		
//SELECT y.id,y.balance,IFNULL(payment.cost,0) payment_cost,IFNULL(payacc.cost,0) payacc_cost,y.`balance`+IFNULL(payment.cost,0)-IFNULL(payacc.cost,0) balanceEnd FROM yii_user y
//LEFT JOIN
//(SELECT y.`user_id`,sum(y.`cost`) cost FROM yii_payacc y WHERE y.is_active=1 AND DATE_FORMAT(y.`work_date`,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m') GROUP BY y.`user_id`) payacc
//ON (y.id=payacc.user_id)
//LEFT JOIN
//(SELECT y.`user_id`,sum(y.`payment_sum`) cost FROM yii_payment y WHERE y.is_active=1 AND DATE_FORMAT(y.`payment_date`,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m') GROUP BY y.`user_id`) payment
//ON (y.id=payment.user_id)
//
//ORDER BY balanceEnd		


//SELECT y.id,y.balance,IFNULL(payment_begin.cost,0)-IFNULL(payacc_begin.cost,0) balanceBegin, IFNULL(payacc.cost,0) payacc_cost, IFNULL(payment.cost,0) payment_cost,IFNULL(payment_begin.cost,0)-IFNULL(payacc_begin.cost,0)+IFNULL(payment.cost,0)-IFNULL(payacc.cost,0) balanceEnd FROM yii_user y
//LEFT JOIN
//(SELECT y.`user_id`,sum(y.`cost`) cost FROM yii_payacc y WHERE y.is_active=1 AND DATE_FORMAT(y.`work_date`,'%Y-%m') = DATE_FORMAT(DATE('2010-06-01'),'%Y-%m') GROUP BY y.`user_id`) payacc
//ON (y.id=payacc.user_id)
//LEFT JOIN
//(SELECT y.`user_id`,sum(y.`payment_sum`) cost FROM yii_payment y WHERE y.is_active=1 AND DATE_FORMAT(y.`payment_date`,'%Y-%m') = DATE_FORMAT(DATE('2010-06-01'),'%Y-%m') GROUP BY y.`user_id`) payment
//ON (y.id=payment.user_id)
//LEFT JOIN
//(SELECT y.`user_id`,sum(y.`cost`) cost FROM yii_payacc y WHERE y.is_active=1 AND y.`work_date` < DATE('2010-06-01') GROUP BY y.`user_id`) payacc_begin
//ON (y.id=payacc_begin.user_id)
//LEFT JOIN
//(SELECT y.`user_id`,sum(y.`payment_sum`) cost FROM yii_payment y WHERE y.is_active=1 AND y.`payment_date` < DATE('2010-06-01') GROUP BY y.`user_id`) payment_begin
//ON (y.id=payment_begin.user_id)
//TODO Запросы на вывод отчета по периодам (заменить дату начала DATE('2010-06-01')
		
	}
}
